﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Diagnostics;

/// <summary>
/// userDao 的摘要说明
/// </summary>
public class userDao
{
    //获取数据库连接串
    private static string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["MySQLConnectionString"].ConnectionString;
    /// <summary>
    /// 获取所有记录
    /// </summary>
    /// <returns>数据List集合</returns>
    public static List<User> FindAll()
    {
        List<User> data = new List<User>();

        MySqlConnection conn = new MySqlConnection(connStr);

        try
        {
            conn.Open();
            string sql = "SELECT * FROM tb_userinfo";
            //构建一个SQL语句命令
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            //获取数据访问的游标
            MySqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                User info = new User();
                info.Id = Convert.ToInt64(rdr["id"]);
                info.AdminName = Convert.ToString(rdr["username"]);
                info.Password = Convert.ToString(rdr["usercode"]);
                data.Add(info);
            }
            conn.Close();
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine("======FindAll方法异常信息=======");
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            Debug.WriteLine("======FindAll方法异常信息=======");
            Debug.WriteLine("数据库未知错误");
            throw (ex);
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }

        return data;

    }

    /// <summary>
    /// 分页获取Info表的所有记录
    /// </summary>
    /// <param name="start">起始位置</param>
    /// <param name="num">获取的记录数</param>
    /// <returns>数据List集合</returns>
    public static List<User> FindAll(int start, int num)
    {
        List<User> data = new List<User>();

        MySqlConnection conn = new MySqlConnection(connStr);

        try
        {
            conn.Open();
            //SQL语句拼接的形式
            string sql = "SELECT * FROM tb_userinfo LIMIT " +
                "" + start + "," + num;
            //构建一个SQL语句命令
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            //获取数据访问的游标
            MySqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                User info = new User();
                info.Id = Convert.ToInt64(rdr["id"]);
                info.AdminName = Convert.ToString(rdr["username"]);
                info.Password = Convert.ToString(rdr["usercode"]);
                data.Add(info);
            }
            conn.Close();
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine("======FindAll方法异常信息=======");
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            Debug.WriteLine("======FindAll方法异常信息=======");
            Debug.WriteLine("数据库未知错误");
            throw (ex);
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }



        return data;

    }
    /// <summary>
    /// 查询是否有数据
    /// </summary>
    /// <param name="id">信息的内部ID序号</param>
    /// <returns>指定的信息记录</returns>
    public static Boolean IsExist(string name)
    {
        
        MySqlConnection conn = new MySqlConnection(connStr);
        Boolean flag = false;
        try
        {
            conn.Open();
            //sql语句使用占位符,更加安全,可在一定程度上防止sql注入
            string sql = "SELECT * FROM tb_userinfo WHERE username=@name";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            //添加对应参数
            cmd.Parameters.AddWithValue("@name", name);
            MySqlDataReader rdr = cmd.ExecuteReader();
            
            if (rdr.HasRows)
            {
                flag = true;
            }
            else
            {
                flag = false;
            }
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine("======IsExist方法异常信息=======");
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            Debug.WriteLine("======IsExist方法异常信息=======");
            Debug.WriteLine("数据库未知错误");
            throw (ex);
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }
        return flag;

    }

    /// <summary>
    /// 按照name获取记录
    /// </summary>
    /// <param name="name">信息的内部username</param>
    /// <returns>指定的信息记录</returns>
    public static User Find(string name)
    {
        User data = new User();
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            conn.Open();
            //sql语句使用占位符,更加安全,可在一定程度上防止sql注入
            string sql = "SELECT * FROM tb_userinfo WHERE username=@name";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            //添加对应参数
            cmd.Parameters.AddWithValue("@name", name);
            MySqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                User info = new User();
                info.Id = Convert.ToInt64(rdr["id"]);
                info.AdminName = Convert.ToString(rdr["username"]);
                info.Password = Convert.ToString(rdr["usercode"]);

            }
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine("======Find(name)方法异常信息=======");
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            Debug.WriteLine("======Find（name)方法异常信息=======");
            Debug.WriteLine("数据库未知错误");
            throw (ex);
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }
        return data;

    }

    /// <summary>
    /// 更新一条记录
    /// </summary>
    /// <param name="data"></param>
    public static void Update(User data)
    {

        MySqlConnection conn = new MySqlConnection(connStr);

        try
        {
            conn.Open();
            string sql = "UPDATE tb_userinfo SET "
                + " usercode=@userCode "
                + " WHERE username=@username"
                ;
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@username", data.AdminName);
            cmd.Parameters.AddWithValue("@usercode", data.Password);
            cmd.Parameters.AddWithValue("@id", data.Id);
            //无返回结果
            cmd.ExecuteNonQuery();
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine("======Update方法异常信息=======");
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            Debug.WriteLine("======Update方法异常信息=======");
            Debug.WriteLine("数据库未知错误");
            throw (ex);
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }

    }
    /// <summary>
    /// 根据用户名删除一条记录
    /// </summary>
    /// <param name="name">用户名</param>
    public static void Delete(string name)
    {
        MySqlConnection conn = new MySqlConnection(connStr);

        try
        {
            conn.Open();
            string sql = "DELETE FROM tb_userinfo WHERE username=@name ";

            MySqlCommand cmd = new MySqlCommand(sql, conn);

            cmd.Parameters.AddWithValue("@name", name);
            //无返回结果
            cmd.ExecuteNonQuery();
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine("======Delete方法异常信息=======");
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            Debug.WriteLine("======Delete方法异常信息=======");
            Debug.WriteLine("数据库未知错误");
            throw (ex);
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }
    }
    /// <summary>
    /// 插入一条记录
    /// </summary>
    /// <param name="data"></param>
    /// <returns></returns>
    public static long Insert(User data)
    {
        long insertId = 0;
        MySqlConnection conn = new MySqlConnection(connStr);

        try
        {
            conn.Open();
            string sql = "INSERT INTO tb_userinfo "
                + "(username,usercode) "
                + " VALUES "
                + "(@username,@usercode)"
                ;
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@username", data.AdminName);
            cmd.Parameters.AddWithValue("@usercode", data.Password);

            //无返回结果
            cmd.ExecuteNonQuery();
            //返回插入记录的id
            insertId = cmd.LastInsertedId;
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine("======Insert方法异常信息=======");
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            Debug.WriteLine("======Insert方法异常信息=======");
            Debug.WriteLine("数据库未知错误");
            throw (ex);
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }
        return insertId;
    }

}